package com.example.dataimport.jobs;

import com.example.dataimport.dao.ExcelImportDao;
import com.example.dataimport.model.ExcelImportJobBO;
import com.example.dataimport.model.TableMappingBO;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelImport{

    ExcelImportDao excelImportDao;

    Map<Integer, String> headerMap = new HashMap<>();
    String folderPath = "";
    String bakPath = "";

    Map<String, Map<String, String>> tableNameToColMap = new HashMap<>();

    public ExcelImport(ExcelImportJobBO excelImportJobBO){
        this.tableNameToColMap = excelImportJobBO.getTableNameToColMap();
        this.folderPath = excelImportJobBO.getExcel_path();
        this.bakPath = excelImportJobBO.getBak_path();
    }

    private String getCellStringValue(Cell cell){
        String value = "";
        if (cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
            if(HSSFDateUtil.isCellDateFormatted(cell)){
                //用于转化为日期格式
                Date d = cell.getDateCellValue();
                DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                value = formater.format(d);
            }else {
                BigDecimal bigDecimal = new BigDecimal(cell.getNumericCellValue());
                value = bigDecimal.toString();
            }
        }else if (cell.getCellType()==Cell.CELL_TYPE_STRING){
            value= cell.getStringCellValue();
        }else if(cell.getCellType()==Cell.CELL_TYPE_BLANK){
        }
        return value;
    }

    public void setFolderPath(String folderPath){
        this.folderPath = folderPath;
    }

    public void setExcelImportDao(ExcelImportDao excelImportDao){
        this.excelImportDao = excelImportDao;
    }

    private String getdbkey(int index, Map<String, String> p_colMap){
        if(p_colMap == null)
            return "";
        if(headerMap.containsKey(index)){
            String excelHeader = headerMap.get(index);
            if(p_colMap.containsKey(excelHeader)){
                return p_colMap.get(excelHeader);
            }
        }
        return "";
    }

    public void getHeaderMap(Row headRow){
        int cellNum = 0;
        if(headRow != null){
            cellNum  = headRow.getLastCellNum();
            for(int count = 0; count < cellNum; count ++){
                Cell aCell = headRow.getCell(count);
                if(aCell != null){
                    String colName = aCell.getStringCellValue();
                    headerMap.put(count,colName);
                }
            }
        }
    }

    public Map<String, String> getAImportRowData(Row dataRow, Map<String, String> p_colMap){
        int cellNum = 0;
        String colValue;
        Map<String, String> dbkeyToValue = new HashMap<>();
        if(dataRow != null){
            cellNum  = dataRow.getLastCellNum();
            for(int count = 0; count < cellNum; count ++){
                Cell aCell = dataRow.getCell(count);
                if(aCell != null){
                    colValue = getCellStringValue(aCell);
                }
                else {
                    colValue = "''";
                }
                if(!"".equals(getdbkey(count, p_colMap))){
                    dbkeyToValue.put(getdbkey(count, p_colMap), colValue);
                }

            }
        }
        return dbkeyToValue;
    }

    public void excelIn(File excelFile){
        InputStream is = null;
        try {
            is = new FileInputStream(excelFile);
            Workbook wb = new HSSFWorkbook(is);
            Sheet sheetObj = wb.getSheetAt(0);
            int rowNum = sheetObj.getLastRowNum();//有效行数
            Row rowObj;

            if(rowNum > 0){
                rowObj = sheetObj.getRow(0);
                getHeaderMap(rowObj);
            }

            for(int count = 1; count < rowNum; count ++){
                rowObj = sheetObj.getRow(count);
                if(rowObj != null){
                    for(Map.Entry<String, Map<String, String>> entry : tableNameToColMap.entrySet()){
                        String tableName = entry.getKey();
                        Map<String,String> colMaps = entry.getValue();
                        Map<String, String> paramMap = getAImportRowData(rowObj, colMaps);
                        excelImportDao.insertARecord(tableName, paramMap);
                    }

                }
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        finally {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    // 读取文件夹下所有文件名
    public static List<File> getFile(File file) {
        List<File> listLocal = new ArrayList<>();
        if (file != null) {
            File[] f = file.listFiles();
            if (f != null) {
                for (int i = 0; i < f.length; i++) {
                    listLocal.add(f[i]);
                }
            } else {
            }
        }
        return listLocal;
    }

    private String getCurrentTimeStr(){
        Date now = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");//可以方便地修改日期格式
        return dateFormat.format( now );
    }


    public void folderScan(){
        File all = new File(folderPath);
        //将全部txt存到list中
        List<File> allPath = getFile(all);
        // 读取txt内容 并转换成List
        for(int i = 0 ;i <allPath.size();i++){
            File f = allPath.get(i);
            excelIn(f);
            File deskFile = new File(bakPath + getCurrentTimeStr() + "-" + f.getName());
            f.renameTo(deskFile);
        }

    }


}
